﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.SqlClient;
using GenericLibrariesToolKit.Enumeration;

namespace GenericLibrariesToolKit
{
    /// <summary>
    /// 生成sql语句的工具类
    /// </summary>
    public class GenerateSqlUtil
    {
        /// <summary>
        /// 生成Persist对应的sql语句
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="fields">字段集合</param>
        /// <returns>sql(insert)语句</returns>
        protected string GeneratePersistSql(Type entityType, List<string> fields)
        {
            //存放sql语句的容器
            StringBuilder sql = new StringBuilder();
            //存放字段的容器
            StringBuilder field = new StringBuilder();
            //存放字段值的容器--采用命名参数赋值
            StringBuilder value = new StringBuilder();
            //根据实体的Type值，从实体管理器中获取对应的Entity对象
            Entity packagedEntity = EntityManager.GetInsatnce().GetEntity(entityType.ToString());

            //遍历字段集
            foreach (string temp in fields)
            {
                field.Append(packagedEntity.ORMDictionary[temp]).Append(",");
                value.Append("@").Append(temp).Append(",");
            }

            //去除最后一个","
            field.Remove(field.Length - 1, 1);
            value.Remove(value.Length - 1, 1);

            //拼接sql语句
            sql.Append("insert into ").Append(packagedEntity.TableName).Append("(").Append(field).Append(")");
            sql.Append("values(").Append(value).Append(")");

            return sql.ToString();
        }

        /// <summary>
        /// 生成Delete(按实体标识符删除)对应的sql语句
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="entityIdValue">实体标识符值</param>
        /// <returns>sql(delete)语句</returns>
        protected string GenerateDeleteSql(Type entityType, object entityIdValue)
        {
            //声明一个StringBuilder容器，用于存放sql语句
            StringBuilder sql = new StringBuilder();
            //根据实体的Type值，从实体管理器中获取对应的Entity对象
            Entity packagedEntity = EntityManager.GetInsatnce().GetEntity(entityType.ToString());

            //根据条件拼接sql语句
            sql.Append("delete from ").Append(packagedEntity.TableName).Append(" where ").Append(packagedEntity.ORMDictionary[packagedEntity.EntityId]).Append("=");
            //赋值
            Assignment(sql, entityIdValue);

            return sql.ToString();
        }

        /// <summary>
        ///  生成Delete(按where条件数组删除)对应的sql语句
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="whereConditions">where条件数组</param>
        /// <returns>sql(delete)语句</returns>
        protected string GenerateDeleteSql(Type entityType, WhereCondition[] whereConditions)
        {
            //声明一个StringBuilder容器，用于存放sql语句
            StringBuilder sql = new StringBuilder();
            //根据实体的Type值，从实体管理器中获取对应的Entity对象
            Entity packagedEntity = EntityManager.GetInsatnce().GetEntity(entityType.ToString());
            //获取sql条件语句
            string conditionSql = GenerateWhereConditionSql(packagedEntity, whereConditions);

            //拼接sql语句
            sql.Append("delete from ").Append(packagedEntity.TableName).Append(conditionSql);
           
            return sql.ToString();
        }

        /// <summary>
        /// 生成Merge对应的sql语句
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="fields">字段集合</param>
        /// <returns>sql(update)语句</returns>
        protected string GenerateMergeSql(Type entityType, List<string> fields)
        {
            //存放sql语句的容器
            StringBuilder sql = new StringBuilder();
            //根据实体的Type值，从实体管理器中获取对应的Entity对象
            Entity packagedEntity = EntityManager.GetInsatnce().GetEntity(entityType.ToString());

            //拼接sql语句
            sql.Append("update ").Append(packagedEntity.TableName).Append(" set ");
            //遍历字段集
            foreach (string temp in fields)
            {
                sql.Append(packagedEntity.ORMDictionary[temp]).Append("=@").Append(temp).Append(",");
            }
            //删除最后一个","
            sql.Remove(sql.Length - 1, 1);

            //拼接条件语句
            sql.Append(" where ").Append(packagedEntity.ORMDictionary[packagedEntity.EntityId]).Append("=@").Append(packagedEntity.EntityId);

            return sql.ToString();
        }

        /// <summary>
        /// 生成Get(按实体标示符获取实体)对应的sql语句
        /// </summary>
        /// <param name="packagedEntity">Entity对象</param>
        /// <param name="entityIdValue">实体标识符值</param>
        /// <returns>sql(select)语句</returns>
        protected string GenerateQuerySql(Entity packagedEntity, object entityIdValue)
        {
            //存放sql语句的容器
            StringBuilder sql = new StringBuilder();

            //拼接sql语句
            sql.Append("select * from ").Append(packagedEntity.TableName).Append(" where ").Append(packagedEntity.ORMDictionary[packagedEntity.EntityId]).Append("=");
            //赋值
            Assignment(sql, entityIdValue);

            return sql.ToString();
        }

        /// <summary>
        /// 生成Query对应的sql语句(包含分页功能)
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="firstResult">记录开始索引，值为-1时表示不使用分页功能</param>
        /// <param name="maxResult">获取最大记录条数，值为-1时表示不使用分页功能</param>
        /// <param name="whereConditions">where条件数组</param>
        /// <param name="orderByDictionary">ordr by 条件字典</param>
        /// <returns>sql(select--分页)语句</returns>
        public string GenerateQuerySql(Type entityType, int firstResult, int maxResult, WhereCondition[] whereConditions, Dictionary<string, string> orderByDictionary)
        {
            //存放sql语句的容器
            StringBuilder sql = new StringBuilder();
             //获取对应的实体
            Entity packagedEntity = EntityManager.GetInsatnce().GetEntity(entityType.ToString());
            //获取实体对应的数据库表名
            string tableName = packagedEntity.TableName;
            //生成内层where条件语句
            string innerWhereSql = GenerateWhereConditionSql(packagedEntity, whereConditions);
            //生成外层where条件语句
            StringBuilder outterWhereSql = new StringBuilder(innerWhereSql);
            outterWhereSql.Replace("where", "and");

            //生成order by 条件语句
            string orderBySql = GenerateOrderByConditionSql(packagedEntity, orderByDictionary);

            //判断是否使用分页功能
            if (firstResult == -1 || maxResult == -1)
            {
                sql.Append("select * from ").Append(tableName).Append(innerWhereSql).Append(orderBySql);
                return sql.ToString();
            }

            //获取实体标识符对应的数据库中表的主键名称
            string id = packagedEntity.ORMDictionary[packagedEntity.EntityId];

            //拼接sql语句
            sql.Append("SELECT TOP ").Append(maxResult).Append(" * FROM ").Append(tableName);
            sql.Append(" WHERE (").Append(id).Append(" NOT IN (SELECT TOP ").Append(firstResult).Append(" ");
            sql.Append(id).Append(" FROM ").Append(tableName).Append(innerWhereSql).Append(orderBySql);
            sql.Append("))").Append(outterWhereSql).Append(orderBySql);

            return sql.ToString();
        }

        /// <summary>
        /// 生成条件语句
        /// </summary>
        /// <param name="entity">实体</param>
        /// <param name="operateConditions">OperateCondition数组</param>
        /// <returns>sql条件字符串</returns>
        protected string GenerateWhereConditionSql(Entity packagedEntity, WhereCondition[] whereConditions)
        {
            //存放sql语句的容器
            StringBuilder sql = new StringBuilder();
            //判断条件是否有效
            if (whereConditions == null || whereConditions.Length < 0) return sql.ToString();

            //拼接sql语句
            sql.Append(" where ");
            //获取对象关系映射字典
            Dictionary<string,string> orm = packagedEntity.ORMDictionary;
            //
            for (int i = 0; i < whereConditions.Length; i++)
            {
                WhereJoinType whereJoinType = whereConditions[i].WhereJoinType;
                switch (whereJoinType)
                {
                    case WhereJoinType.AND:
                        sql.Append(" and ");
                        break;
                    case WhereJoinType.OR:
                        sql.Append(" or ");
                        break;
                    case WhereJoinType.NULL:
                        sql.Append(" ");
                        break;
                }
                //拼接字段
                sql.Append(orm[whereConditions[i].EntityField]);
                //定义匹配模式变量
                MatchType matchType = whereConditions[i].FieldMatchType;
                //获取属性值
                object entityFieldValue = whereConditions[i].FieldMatchValue;
                //拼接模糊查询方式
                switch (matchType)
                {
                    case MatchType.EQUAL:
                        sql.Append(" = ");
                        Assignment(sql, entityFieldValue);
                        break;
                    case MatchType.UNEQUAL:
                        sql.Append(" != ");
                        Assignment(sql, entityFieldValue);
                        break;
                    case MatchType.GREATER:
                        sql.Append(" > ");
                        Assignment(sql, entityFieldValue);
                        break;
                    case MatchType.LOWER:
                        sql.Append(" < ");
                        Assignment(sql, entityFieldValue);
                        break;
                    case MatchType.LIKE:
                        sql.Append(" like '").Append(entityFieldValue).Append("' ");
                        break;
                    case MatchType.IN://entityFieldValue形式 1,2,3或者'a','b','c'
                        sql.Append(" in(").Append(entityFieldValue).Append(") ");
                        break;
                    case MatchType.NOT_IN:
                        sql.Append(" not in(").Append(entityFieldValue).Append(") ");
                        break;
                }
            }

            return sql.ToString();
        }

        /// <summary>
        /// 生成order by sql语句
        /// </summary>
        /// <param name="packagedEntity">封装的实体</param>
        /// <param name="orderByDictionary">order by 排序字典</param>
        /// <returns>order by sql语句</returns>
        protected string GenerateOrderByConditionSql(Entity packagedEntity, Dictionary<string, string> orderByDictionary)
        {
            //存放sql语句的容器
            StringBuilder sql = new StringBuilder();
            //获取实体关系映射字典
            Dictionary<string, string> ormDictionary = packagedEntity.ORMDictionary;

            if (orderByDictionary == null || orderByDictionary.Count() < 0) return sql.ToString();
            
            //拼接sql语句
            sql.Append(" order by ");
            foreach (KeyValuePair<string, string> pair in orderByDictionary)
            {
                sql.Append(ormDictionary[pair.Key]).Append(" ").Append(pair.Value).Append(",");
            }

            //删除最后一个","
            sql.Remove(sql.Length - 1, 1);

            return sql.ToString();
        }

        /// <summary>
        /// 根据条件给sql语句赋值
        /// </summary>
        /// <param name="sql">sql语句</param>
        protected void Assignment(StringBuilder sql, object entityFieldValue)
        {
            //如果值是string类型
            if (entityFieldValue is string)
            {
                sql.Append("'").Append(entityFieldValue).Append("'");
            }
            else //非string类型
            {
                sql.Append(entityFieldValue);
            }
        }

        /// <summary>
        /// 生成获取数据库中指定表中的记录条数sql语句
        /// </summary>
        /// <param name="entityType">实体类型</param>
        /// <param name="whereConditions">where条件数组</param>
        /// <returns>sql语句</returns>
        protected string GenerateCountSql(Type entityType, WhereCondition[] whereConditions)
        {
            //存放sql语句的容器
            StringBuilder sql = new StringBuilder();
            //根据实体类型从实体管理器中获取对应的实体
            Entity packetageEntity = EntityManager.GetInsatnce().GetEntity(entityType.ToString());
            //生成where条件语句
            string whereSql = GenerateWhereConditionSql(packetageEntity, whereConditions);
            //拼接sql语句
            sql.Append("select count(*) from ").Append(packetageEntity.TableName).Append(" ").Append(whereSql);

            return sql.ToString();
        }

        /// <summary>
        /// 生成SqlParameter参数数组
        /// </summary>
        /// <param name="paramDictionary">参数字典</param>
        /// <returns>SqlParameter参数数组</returns>
        protected SqlParameter[] GenerateSqlParameter(Dictionary<string, object> paramDictionary)
        {
            //声明一个空的SqlParameter数组
            SqlParameter[] sqlParameter = new SqlParameter[paramDictionary.Count()];

            //数组游标
            int counter = 0;
            //遍历参数字典给SqlParameter数组赋值
            foreach (KeyValuePair<string, object> entry in paramDictionary)
            {
                sqlParameter[counter] = new SqlParameter("@" + entry.Key, entry.Value);
                //游标向下移动一位
                counter++;
            }

            return sqlParameter;
        }

    }
}
